-- Demo script for the session Introduction to Regular Expressions.
-- © 2025 Erland Sommarskog.

USE tempdb;
go
-- Create demo database. Two things are important with the collation:
-- It is case-insensitive and it is UTF8, so that we have Unicode support
-- in varchar and do not need N before string literals.
DROP DATABASE IF EXISTS regexp;
CREATE DATABASE regexp COLLATE Polish_100_CI_AS_SC_UTF8;
go
USE regexp;
go
-- Create simple demo table and add some words.
DROP TABLE IF EXISTS words;
CREATE TABLE words (word varchar(40) NOT NULL
                    --CONSTRAINT pk_words PRIMARY KEY (word)  
);
go
INSERT words (word)
  VALUES('A'), ('apple'), ('banana'), ('box'), ('Grimsby'), ('maffia');
go
SELECT word FROM words;
go
-- Comparing LIKE and regexp_like.
SELECT word FROM words WHERE word LIKE 'a';
SELECT word FROM words WHERE regexp_like(word, 'a');
-- Results are entirely disjunct. LIKE only matches "A", since there is
-- no trailing or leading wildcard. Regexps on the other hand, by default,
-- match anywhere in the string. LIKE understands collations and here we 
-- have a CI collation. Regexps default to be case-sensitive and are blind 
-- to collations.
go
-- Add trailing/leading wildcard with LIKE, and 'i' flag to regexp_like 
-- to force case-insensitive. Results are now the same.
SELECT word FROM words WHERE word LIKE '%a%';
SELECT word FROM words WHERE regexp_like(word, 'a', 'i');
go
-- We can add ^ to anchor a regexp to the start of the string.
SELECT word FROM words WHERE word LIKE 'a%';
SELECT word FROM words WHERE regexp_like(word, '^a', 'i');
go
-- Add $ matches at the end of the string.
SELECT word FROM words WHERE word LIKE '%a';
SELECT word FROM words WHERE regexp_like(word, 'a$', 'i');
go
-- We can use both.
SELECT word FROM words WHERE regexp_like(word, '^a$', 'i');
go
-- Add one more "word" and try to find that word.
INSERT words (word) VALUES ('...^t$::7');
SELECT word FROM words WHERE regexp_like(word, '^t$');
go
-- We got no match, because ^ and $ have special meaning. We need to 
-- escape them with backslash (\) to match an actual caret or dollar.
SELECT word FROM words WHERE regexp_like(word, '\^t\$');
go
-- But don't escape the "t"! "\" + alphanumeric is the other way around,
-- it may have some special meaning. Therefore, thisreturns no rows, 
-- because \t has a special meaning.
SELECT word FROM words WHERE regexp_like(word, '\^\t\$');
-----------------------------------------------------
-- Add some more words for the next lesson.
INSERT words(word)
  VALUES('Baal'), ('pasta'), ('résumé'), ('Super-8'), 
        ('tableware'), ('teststation'), ('testtube');
go
SELECT word FROM words;
go
-- In a regular expression, we can use brackets to enclose a 
-- group of alternate characters to match. This works exactly
-- the same way as LIKE.
-- List words that end in "x", "y" or a digit:
SELECT word FROM words WHERE word LIKE '%[yx0-9]';
SELECT word FROM words WHERE regexp_like(word, '[yx0-9]$');
go
-- List words that end in anything else than "x", "y" or a digit.
SELECT word FROM words WHERE word LIKE '%[^yx0-9]';
SELECT word FROM words WHERE regexp_like(word, '[^yx0-9]$');
go
-- Beware, though, that there can be differences due to collation.
-- Return words that end in a character between "d" and "l".
SELECT word FROM words WHERE word LIKE '%[d-l]';
SELECT word FROM words WHERE regexp_like(word, '[d-l]$', 'i');
-- LIKE understands collations, so it considers "é" to be between "d" and "l".
-- Regexps goes by code point, and the code point for "d" is 100, "l" is 108.
-- "é" = 233.
go
-------------------------------------------------
-- In LIKE patterns, underscore stands for "any character", exactly once.
-- In regular expressions the dot "." serves that role.
SELECT word FROM words WHERE word LIKE '%a_a%';
SELECT word FROM words WHERE regexp_like(word, 'a.a');
go
-- Say that we want to find words with at least two "a" in them.
-- Here are two ways to do that.
SELECT word FROM words WHERE word LIKE '%a%a%';
SELECT word FROM words WHERE regexp_count(word, 'a') >= 2;
go
-- Here is a third one that introduces a new concept, a quantifier.
SELECT word FROM words WHERE regexp_like(word, 'a.*a');
-- The asterisk here means "previous regular expression zero or more times".
-- That is, "a" followed by any character any number of times. 
-- Thus, LIKE's "%" is ".*" in regular expressions. But the quantifier
-- is a lot more powerful.

-- There are more quantifiers! Very commonly used is "+" meaning "previous
-- regexp, one or more times". 
-- So this means: All words with two "a" separated by at least one character.
SELECT word FROM words WHERE regexp_like(word, 'a.+a');

-- The question mark("?") means previous regexp zero or one time.
SELECT word FROM words WHERE regexp_like(word, 'a.?a');
go
-- Beside these shortcuts, there is also a general form with braces.
-- This means: Words with two "a" separated by at least three characters.
SELECT word FROM words WHERE regexp_like(word, 'a.{3,}a');
-- Banana? There is only one character between the "a"s. Yes, between the
-- first and the second, and between the second and third "a". But between
-- the first and the third there are three. We said "any character", and that
-- includes "a".

-- If we don't want that, we should not use ".", but [^a] to say "any 
-- character but 'a'":
SELECT word FROM words WHERE regexp_like(word, 'a[^a]{3,}a');
go
-- More examples: previous regular expression an exact number of times,
-- and previous regular expression at least n and at most m times.
SELECT word FROM words WHERE regexp_like(word, 'a[^a]{2}a');
SELECT word FROM words WHERE regexp_like(word, 'a[^a]{2,3}a');
go
-- Let's look at a practical example, which also shows regexp_split_to_table
-- in action. Split a text with inconsistent spacing into words. The 
-- purpose of quotename is to show that there are no trailing or leading 
-- spaces.
SELECT quotename(value), ordinal 
FROM   regexp_split_to_table('A   text   with  lots of   spaces', ' +')
ORDER  BY ordinal;
go
-- This example splits a comma-separated list with inconsistent spacing 
-- into table format, with no trailing or leading spaces.
SELECT quotename(value), ordinal
FROM   regexp_split_to_table('101, 221,43,14,  85,  6', ', *')
ORDER  BY ordinal;
go
-- These two examples demonstrate how parentheses can be used to control
-- precedence. 
-- This means: Find words with two or more "st" in succession.
SELECT word FROM words WHERE regexp_like(word, '(st){2,}');
-- While this means: "s" followed by two or more "t".
SELECT word FROM words WHERE regexp_like(word, 'st{2,}');
go
--------------------------------------------------------
-- For the next lesson, we need a new table with sentences.
DROP TABLE IF EXISTS sentences;
CREATE TABLE sentences (id       int NOT NULL,
                        sentence varchar(200) NOT NULL,
								CONSTRAINT pk_sentences PRIMARY KEY (id)
);
INSERT sentences (id, sentence)
  VALUES (1, 'Keith tells Jack to go to school'),
         (2, 'Keith says that Jill is winning the game'),
			(3, 'Jill sees Laura quilting a cloth'),
			(4, 'Laura hears Jack and Jill singing in the park'),
			(5, 'William is visiting Jill'),
			(6, 'Laura gave the jack of clubs to William'),
			(7, 'Keith saw Laura hitting the target'),
			(8, 'Laura chides Keith for belittling Jack')
go
-- Here is another metacharacter "|", which permits you specify alternate
-- regular expressions that can match.
-- This means: Find all sentences where any of Jack or Jill appears.
SELECT id, sentence FROM sentences 
WHERE  regexp_like(sentence, 'Jack|Jill');
-- Note that we did not match the sentence with "jack of clubs" since
-- regular expressions by default are case-sensitive.

-- Say that we only want to find sentences where Jack or Jill appear
-- in the middle. Then we can do this.
SELECT id, sentence FROM sentences 
WHERE regexp_like(sentence, ' (Jack|Jill) ');

-- Note the parentheses. They are needed, because "|" has the lowest
-- operator precedence. Look here what happens if we leave them out:
SELECT id, sentence FROM sentences 
WHERE regexp_like(sentence, ' Jack|Jill ');
-- Now we also get sentences ending with Jack or starting with Jill.

-- The alternates do not have to be fixed strings, but can be longer
-- expressions. This means: Strings with Keith before Jack or strings
-- with Jill before Laura.
SELECT id, sentence FROM sentences 
WHERE regexp_like(sentence, '(Keith.+Jack|Jill.+Laura)');

-- If we use quantifiers, it matters where we place them.
-- This means: Two "i" separated by one or more of "l" or "t" in any order.
-- We also introduce regexp_substr, which returns the actual match.
DECLARE @pat varchar(20) = 'i(l|t)+i';
SELECT id, sentence, regexp_substr(sentence, @pat)
FROM sentences 
WHERE regexp_like(sentence, @pat);
go
-- This means: Two "i" separated by a sequence of l:s or a sequence of t:s.
-- So "quilting" and "belittling" do not match.
DECLARE @pat varchar(20) = 'i(l+|t+)i';
SELECT id, sentence, regexp_substr(sentence, @pat)
FROM sentences 
WHERE regexp_like(sentence, @pat);
go
-------------------------------------------------------
-- We will now work with regexp_replace. This is the example from the
-- beginning of the session. Collapse multiple spaces into a single one.
SELECT regexp_replace
       ('A    text with   extra  space', ' +', ' ');
go
-- The replacement string can refer back to the match. "\&" means "the entire
-- match". Here we put asterisks around occurrences of Jack or Jill.
SELECT regexp_replace(sentence, '(Jack|Jill)', '*\&*')
FROM   sentences;
go
-- Say that we only want to do that when Jack or Jill are in the middle of
-- the sentence. We might try:
SELECT regexp_replace(sentence, ' (Jack|Jill) ', '*\&*')
FROM   sentences;
-- But the asterisks appear in the wrong place, because the matches include
-- the spaces.
go
-- The solution: In regular expression, parentheses serve two purposes: 
-- 1) Precedence order 2) Define capture groups. We can refer to the first
-- parens as \1, the next as \2 etc. This leads is to:
SELECT regexp_replace(sentence, ' (Jack|Jill) ', ' *\1* ')
FROM   sentences;
go
-- Here is an advanced example. In sentences with a boy before a girl, 
-- swap them, so that the girl comes before the boy. The purpose of the
-- first alternate is to cover that the boy's name may come first or in the
-- middle of a sentence, and the corresponding applies to the last alternate.
DECLARE @pat varchar(200) = '(^| )(Keith|William|Jack)( .* )(Jill|Laura)( |$)';
SELECT sentence, regexp_replace(sentence, @pat, '\1\4\3\2\5')
FROM   sentences
WHERE  regexp_like(sentence, @pat);
go
-- If you want regexp_replace to be case-insensitive, the "flags" parameter
-- is the sixth. The fourth is starting position, and the fifth is occurrence,
-- where 0 means all occurrences.
SELECT regexp_replace(sentence, ' (Jack|Jill) ', ' *\1* ', 1, 0, 'i')
FROM   sentences;

------------------------------------------------------
-- For the next lession, we need yet another table!
DROP TABLE IF EXISTS teststrings;
CREATE TABLE teststrings(
       id int NOT NULL,
       str varchar(100) NOT NULL,
       CONSTRAINT pk_teststrings PRIMARY KEY (id)
);
go
-- Note that some of the strings include characters outside ASCII.
INSERT teststrings(id, str)
   VALUES(1, '10 µm'),
         (2, 'résumé'),
         (3, 'słońce'),
         (4, '"Please", she said, "pass the biscuits!"'),
         (5, 'ERR_MSG_NOT_FOUND'),
         (6, 'X870E122BC'),
         (7, 'A    text with   extra  space'),
         (8, 'Fancy digits 𝟘𝟙𝟚𝟛𝟜𝟝𝟞𝟟𝟠𝟡'),
         (9, 'άλφα'),
         (10, 'Слава Україні!'),
         (11, '中目黒'),
         (20, 'tab:' + nchar(9)),
         (21, 'CR:' + nchar(13)),
         (22, 'LF:' + nchar(10)),
         (23, 'FF:' + nchar(12)),
         (24, 'space:' + nchar(32)),
         (25, 'NBSP:' + nchar(160));
go
SELECT * FROM teststrings;
go
-- Escape sequences for control characters.
SELECT *, '\t' FROM teststrings WHERE regexp_like(str, '\t'); 
SELECT *, '\r' FROM teststrings WHERE regexp_like(str, '\r'); 
SELECT *, '\n' FROM teststrings WHERE regexp_like(str, '\n'); 
SELECT *, '\f' FROM teststrings WHERE regexp_like(str, '\f'); 
go
-- Escape sequences to match characters by their hex code. We need braces
-- for values > 0xFF.
SELECT *, '\xA0' FROM teststrings WHERE regexp_like(str, '\xA0');
SELECT *, '\x{4E2D}' FROM teststrings WHERE regexp_like(str, '^\x{4E2D}');
go
-- There are a number of escape sequences for character classes.
-- \s is for white-space characters. It is ASCII only, so it does not
-- include the hard space. (Which, depending on the situation, may be 
-- what you want. Or not.)
SELECT * 
FROM   teststrings
WHERE  regexp_like(str, '\s$');
go
-- If you also want to include the hard space in your search for spaces, 
-- you can use brackets, and you can put \s in the brackets:
SELECT * 
FROM   teststrings
WHERE  regexp_like(str, '[\s\xA0]$');
go
-- For all the character-class sequences, the uppercase version is the 
-- negation. So this lists all test strings not ending with a white space.
SELECT * 
FROM   teststrings
WHERE  regexp_like(str, '\S$');
go
-- \d matches digits, essentially a shortcut for [0-9]. You may note that
-- it does not match the "fancy digits". Here we use regexp_substr to get 
-- the first and second match.
DECLARE @pat varchar(20) = '\d+';
SELECT id, str, regexp_count(str, @pat) AS Cnt, 
           regexp_substr(str, @pat, 1, 1) AS "1st", 
           regexp_substr(str, @pat, 1, 2) AS "2nd"
FROM   teststrings
WHERE  regexp_like(str, @pat);
go
-- \D is the negation. This lists all rows that starts with anything else
-- than a digit. Only the first row is excluded.
SELECT id, str
FROM   teststrings
WHERE  regexp_like(str, '^\D')
go
-- To match letters in general, we can use \pL. (Observe case: lowercase "p"
-- and uppercase "L"). This gives all sequences of letters. Note that
-- the Greek, Ukrainian and Japanese strings are returned.
DECLARE @pat varchar(20) = '\pL+';
SELECT id, str, regexp_count(str, @pat) AS Cnt, 
           regexp_substr(str, @pat, 1, 1) AS "1st", 
           regexp_substr(str, @pat, 1, 2) AS "2nd"
FROM   teststrings
WHERE  regexp_like(str, @pat);
go
-- \p stands for Unicode category of which there are a bunch. There are also
-- subcategories. For instance Lu for uppercase and Ll for lowercase. So
-- the below gives all sequences that start with an uppercase character
-- followed by one or more lowercase character.
DECLARE @pat varchar(20) = '\p{Lu}\p{Ll}+';
SELECT id, str, regexp_count(str, @pat) AS Cnt, 
           regexp_substr(str, @pat, 1, 1) AS "1st", 
           regexp_substr(str, @pat, 1, 2) AS "2nd"
FROM   teststrings
WHERE  regexp_like(str, @pat);
go
-- Negation works here as well. \PL+ means "one or more non-letters". Here
-- we use regexp_matches to get all matches.
DECLARE @pat varchar(20) = '\PL+';
SELECT m.match_id, quotename(m.match_value), t.id, t.str
FROM   teststrings t
CROSS  APPLY regexp_matches(t.str, @pat) m
ORDER  BY t.id, m.match_id;
go
-- There are many more of those Unicode categories. See the RE2 reference,
-- https://github.com/google/re2/wiki/Syntax for a full list. 
go
-- Here is the ugly one. \w is supposed to return "word" characters, that is
-- letters, digits and underscore.
SELECT m.match_id, m.match_value, t.id, t.str
FROM   teststrings t
CROSS  APPLY regexp_matches(t.str, '\w+') m
ORDER  BY t.id, m.match_id;
-- In SQL Server it is crippled and is restricted to ASCII. Don't use!
-- In Perl, Python, Powershell, Postgres, MySQL and others, \w support
-- Unicode.
go
-- Only to show that \W is equally crippled. It matches anything outside
-- ASCII, regardless if it is a word character or not.
SELECT str
FROM   teststrings
WHERE  regexp_like(str, '^\W+$');
-- Returns the Greek, Ukranian and Japanese strings.
go
-- POSIX character classes. Also ASCII-crippled. And a bulky syntax. Avoid!
SELECT m.match_id, m.match_value, t.id, t.str
FROM   teststrings t
CROSS  APPLY regexp_matches(t.str, '[[:alpha:]]+') m
ORDER  BY t.id, m.match_id;
go
---------------------------------------------------
-- Some lessons about multi-line strings.
DROP TABLE IF EXISTS multiline;
CREATE TABLE multiline(id   int,
                       text varchar(2000),
							  CONSTRAINT pk_multiline PRIMARY KEY (id));
go
-- Yes, that is the first half of the first verse of Smoke on the Water!
INSERT multiline(id, text)
   VALUES(1, 
'We all came down to Montreux
On the Lake Geneva shorline
To make records with a mobile
We did not have much time');
go
-- We expect this to match the text above, since the dot stands for "any
-- character".
SELECT id, text
FROM   multiline
WHERE  regexp_like(text, '^We.*time$');
-- But it doesn't! You see, the dot actually stands for "any character 
-- but newline".
go
-- We can save the show with an alternate:
SELECT id, text
FROM   multiline
WHERE  regexp_like(text, '^We(.|\n)*time$');
go
-- This can get bulky, if we need to do it many times. For this reason, we
-- we can use the "s" flag which means "dot also matches newline".
SELECT id, text
FROM   multiline
WHERE  regexp_like(text, '^We.*time$', 's');
go
-- We can combine the "i" and "s" flags.
SELECT id, text
FROM   multiline
WHERE  regexp_like(text, '^WE.*TIME$', 'is');
go
-- At this point open multiline.sql to load the second half or the 
-- Smoke on the Water verse.
go
-- Split the text into lines. In Windows CR-LF is the line separator, so
-- \r\n seems like the right thing:
SELECT ml.id, s.value, s.ordinal
FROM   multiline ml
CROSS  APPLY regexp_split_to_table(ml.text, '\r\n') s
ORDER  BY ml.id, s.ordinal;
-- But second row did not split!
go
-- Multiline.sql is saved in Unix format, and on Unix lines are terminated
-- by LF (\n) alone. You can never be sure what format you have, so when
-- splitting into lines, use this pattern to make the CR optional.
SELECT ml.id, s.value, s.ordinal
FROM   multiline ml
CROSS  APPLY regexp_split_to_table(ml.text, '\r?\n') s
ORDER  BY ml.id, s.ordinal;
-- If you also want to cover (old) Mac format, where lines are separated by
-- CR alone, this is left as an exercise to the reader.
---------------------------------------------
